from openpyxl import load_workbook
import os
import time
class ExcelUtil:
    def __init__(self,file_name):
        self.file_name = file_name
        self.wb = load_workbook(file_name)
        self.sheet = self.wb.active
        self.ncols = self.sheet.max_column
        self.price_col = 4 #定价所在的列
        self.discount_col = 5 #折扣所在的行

    def cost(self,row,cost_col):
        """
        进价计算
        :param row: 开始行
        :param column: 插入的列
        :return:
        """
        discount = self.sheet.cell(row=row,column=self.discount_col).coordinate#折扣
        price = self.sheet.cell(row=row, column=self.price_col).coordinate#定价
        cost = '=' + price + '*' + discount + '*0.01'
        return self.sheet.cell(row=row, column=cost_col, value=str(cost)).coordinate  # 进价

    def sales_discounts(self,row,increase_discounts_col,sales_discounts_col,value=5):
        """
        销售折扣
        :param row: 开始行
        :param value:增折数值
        :return:
        """
        discount = self.sheet.cell(row=row,column=5).coordinate#折扣
        increase = self.sheet.cell(row=row,column=increase_discounts_col,value=value).coordinate#增折
        dis_value = '='+discount+'+'+increase
        return  self.sheet.cell(row=row,column=sales_discounts_col,value=dis_value).coordinate#销售折扣

    def sales(self,row,sales_discounts_cell,sales_col):
        """
        销售价
        :param row:开始行
        :param sales_discounts:销售折扣
        :return:
        """
        price = self.sheet.cell(row=row, column=self.price_col).coordinate
        sales = '=' + price + '*' + sales_discounts_cell + '*0.01'
        return self.sheet.cell(row=row,column=sales_col,value=sales).coordinate


    def profit(self,row,cost_cell,sales_cell,profit_col):
        """
        利润
        :param row:开始插入的行
        :param cost_cell: 成本单元格坐标
        :param sales_cell:销售价单元格坐标
        :return:返回利润单元格坐标
        """
        value = '='+sales_cell+'-'+cost_cell
        return self.sheet.cell(row=row,column=profit_col,value=value).coordinate
    def platform(self,row,sales_cell,platform_col):
        """
        平台扣除费用计算
        :param row: 开始行
        :param sales_cell 售价单元坐标
        :param platform_col: 平台扣除费用所在列
        :return: 平台扣除费用单元坐标
        """
        value = '='+ sales_cell+'*0.05'
        return self.sheet.cell(row=row,column=platform_col,value=value).coordinate
    def remaining(self,row,remaining_col,profit_cell,platform_cell):
        """
        平台扣除后利润剩余计算
        :param row: 开始行
        :param remaining_col:剩余值所在列
        :param profit_cell: 利润单元坐标
        :param platform_cell: 平台扣除费用单元坐标
        :return: 剩余利润的单元坐标
        """
        value = '='+profit_cell + '-' + platform_cell
        return self.sheet.cell(row=row,column=remaining_col,value=value).coordinate

    def compute(self):
        nrows = self.sheet.max_row
        cost_col = self.ncols + 1#成本所在列
        increase_discounts_col = self.ncols + 2#增折所在列
        sales_discounts_col = self.ncols + 3#销售折扣
        sales_col = self.ncols + 4 #销售价所在列
        profit_col = self.ncols + 5 #利润
        platform_col = self.ncols + 6 #平台扣除费用
        remaining_col = self.ncols + 7 #剩余利润
        self.sheet.cell(row=1, column=cost_col, value='进价')
        self.sheet.cell(row=1, column=increase_discounts_col, value='增折')
        self.sheet.cell(row=1, column=sales_discounts_col, value='销售折扣')
        self.sheet.cell(row=1, column=sales_col, value='销售价')
        self.sheet.cell(row=1, column=profit_col, value='利润')
        self.sheet.cell(row=1, column=platform_col, value='平台')
        self.sheet.cell(row=1, column=remaining_col, value='剩余')

        for row in range(1,nrows):
            nrow = row + 1
            cost_cell = self.cost(row=nrow,cost_col=cost_col)
            sales_discounts_cell = self.sales_discounts(row=nrow,increase_discounts_col=increase_discounts_col,sales_discounts_col=sales_discounts_col)
            sales_cell = self.sales(row=nrow,sales_discounts_cell=sales_discounts_cell,sales_col=sales_col)
            profit_cell = self.profit(row=nrow,cost_cell=cost_cell,sales_cell=sales_cell,profit_col=profit_col)
            platform_cell = self.platform(row=nrow,sales_cell=sales_cell,platform_col=platform_col)
            remaining_cell = self.remaining(row=nrow,remaining_col=remaining_col,profit_cell=profit_cell,platform_cell=platform_cell)
        file_name = self.sheet.title+'.xlsx'
        self.wb.save('/Users/hfy/Desktop/更新书目/'+file_name)

if __name__=='__main__':

    path = os.getcwd()
    fies_list = os.listdir(path)
    for files in fies_list:
        res = "xlsx" in files
        if res :
            time.sleep(30)
            excelUtil = ExcelUtil(file_name=files)
            excelUtil.compute()
            print('完成:%s'%files)